<?php
class Search_Model_Rosenzu extends Apm_Model_Base{

	/**
	 * 路線図物件数取得
	 *
	 * @access    public
	 * @return    Array 路線図物件数データ
	 */
	public function getBukkenCntList()
	{
		$sql = $this->generateBukkenCntSql();
		return $this->dbGetList($sql, array());
	}

	/**
	 * 路線図物件数取得SQL
	 *
	 * @access    private
	 * @return    String SQL
	 */
	private function generateBukkenCntSql()
	{
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "  A05_ROSENZU_CD";
		$sql .= " ,A05_BUKKEN_CNT";
		$sql .= " FROM";
		$sql .= "  A05ROSENZU_BUKKEN_COUNT";
		
		return $sql;
	}

	/**
	 * 路線図駅名検索
	 *
	 * @access    public
	 * @return    Array 駅データ
	 */
	public function getEkiList($params)
	{
		$db_params = array();
		$sql = $this->generateEkiSql($params, $db_params);
		//echo $sql;
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 路線図駅名検索SQL
	 * 駅ﾏｽﾀより指定ｷｰﾜｰﾄﾞを持つ駅情報を取得する
	 *
	 * @access    private
	 * @return    String SQL
	 */
	private function generateEkiSql($params, &$db_params)
	{
		// キーワード
		$db_params['strKeyWord'] = $params['ekiword'];
		// ページ番号
		$db_params['page'] = $params['page_no'];
		// SQL
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M03_ROSENZU_EKI_CD ";
		$sql .= "    ,M03_EKI_NAME ";
		$sql .= "    ,substr(ENSEN_LIST, 1, length(ENSEN_LIST)-1) as ENSEN_LIST ";
		$sql .= "    ,M03_TODOUFUKEN_CD ";
		$sql .= "    ,M43_TODOUFUKEN ";
		$sql .= "FROM ";
		$sql .= "    ( ";
		$sql .= "        SELECT ";
		$sql .= "             M03.M03_ROSENZU_EKI_CD ";
		$sql .= "            ,M03.M03_EKI_NAME ";
		$sql .= "            ,Fpl_GET_ROSENZU_ENSEN_LIST(M03.M03_ROSENZU_EKI_CD) AS ENSEN_LIST ";
		$sql .= "            ,M03.M03_TODOUFUKEN_CD ";
		$sql .= "            ,M43.M43_TODOUFUKEN";
		$sql .= "            ,ROW_NUMBER() over (order by M03.M03_TODOUFUKEN_CD, M03.M03_ROSENZU_EKI_CD) AS LINE ";
		$sql .= "        FROM";
		$sql .= "             M43TODOUFUKEN M43";
		$sql .= "            ,( ";
		$sql .= "                SELECT ";
		$sql .= "                     M03_ROSENZU_EKI_CD ";
		$sql .= "                    ,M03_EKI_NAME ";
		$sql .= "                    ,M03_TODOUFUKEN_CD ";
		$sql .= "                FROM ";
		$sql .= "                     M03EKI ";
		$sql .= "                WHERE ";
		$sql .= "                    M03_EKI_NAME LIKE '%' || :strKeyWord || '%' ";
		$sql .= "                GROUP BY ";
		$sql .= "                     M03_ROSENZU_EKI_CD ";
		$sql .= "                    ,M03_EKI_NAME ";
		$sql .= "                    ,M03_TODOUFUKEN_CD ";
		$sql .= "            ) M03 ";
		$sql .= "        WHERE ";
		$sql .= "            M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		$sql .= "    ) ";
		$sql .= "WHERE ";
		$sql .= "    LINE between ((:page - 1)*20)+1 AND (:page * 20) ";
		$sql .= "ORDER BY ";
		$sql .= "    LINE ";

		return $sql;
	}

	/**
	 * 路線図駅名検索（件数）
	 *
	 * @access    public
	 * @return    Array 件数
	 */
	public function getEkiCount($params)
	{
		$db_params = array();
		$sql = $this->generateEkiCountSql($params, $db_params);
		//echo $sql;
		return $this->dbGetData($sql, $db_params);
	}

	/**
	 * 路線図駅名検索SQL
	 * 駅ﾏｽﾀより指定ｷｰﾜｰﾄﾞを持つ駅情報を取得する
	 *
	 * @access    private
	 * @return    String SQL
	 */
	private function generateEkiCountSql($params, &$db_params)
	{
		// キーワード
		$db_params['strKeyWord'] = $params['ekiword'];
		// SQL
		$sql  = "";
		$sql .= "        SELECT ";
		$sql .= "             COUNT(*) AS CNT ";
		$sql .= "        FROM";
		$sql .= "             M43TODOUFUKEN M43";
		$sql .= "            ,( ";
		$sql .= "                SELECT ";
		$sql .= "                     M03_ROSENZU_EKI_CD ";
		$sql .= "                    ,M03_EKI_NAME ";
		$sql .= "                    ,M03_TODOUFUKEN_CD ";
		$sql .= "                FROM ";
		$sql .= "                     M03EKI ";
		$sql .= "                WHERE ";
		$sql .= "                    M03_EKI_NAME LIKE '%' || :strKeyWord || '%' ";
		$sql .= "                GROUP BY ";
		$sql .= "                     M03_ROSENZU_EKI_CD ";
		$sql .= "                    ,M03_EKI_NAME ";
		$sql .= "                    ,M03_TODOUFUKEN_CD ";
		$sql .= "            ) M03 ";
		$sql .= "        WHERE ";
		$sql .= "            M03.M03_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";

		return $sql;
	}

	/**
	 * 表示用にデータを変換（駅一覧・ページング）
	 *
	 * @access    public
	 * @param     array パラメータ
	 * @param     array 検索データ
	 * @return    Array ページングデータ
	 */
	public function convertEkiList($params, $data)
	{
		$RosenzuData = array();
		
		// 検索ワード
		$RosenzuData['ekiword'] = $params['ekiword'];
		// 現在のページ数
		$RosenzuData['page'] = $params['page_no'];
		// 表示件数を算出
		$page    = intval($params['page_no']);			// 現在のページ
		$eki_cnt = intval($data['ekiCount']['CNT']);	// 検索結果数
		$eki_st = (($page - 1) * 20) + 1;
		$eki_ed = $page * 20;
		$RosenzuData['eki_st'] = $eki_st;					// 表示開始値
		$RosenzuData['eki_ed'] = min($eki_ed, $eki_cnt);	// 表示終了値
		// ページング成形
		$page_ls = ceil($eki_cnt / 20);			// 最終頁
		$page_st = max(1, $page - 2);
		$page_ed = min($page + 2, $page_ls);
		$page_bf = ($page > 1) ? $page - 1 : 0;			// 前のページへ
		$page_af = ($page < $page_ls) ? $page + 1 : 0;	// 次のページへ
		$RosenzuData['paging'] = array();
		$RosenzuData['paging']['page_bf'] = $page_bf;
		$RosenzuData['paging']['page_af'] = $page_af;
		$RosenzuData['paging']['page_li'] = array();
		for ($i=0; $i<=($page_ed-$page_st); $i++) {
			$RosenzuData['paging']['page_li'][$i] = $i + $page_st;
		}
		
		return $RosenzuData;
	}

}
?>
